package ysw.jdbc;
import ysw.dao.AppointmentDao;
import ysw.databaseconnection.*;
import java.sql.*;
import ysw.vo.Appointment;
import java.util.ArrayList;
import java.util.List;

public class AppointmentJdbc implements AppointmentDao {
     private  Connection conn=null;
    private PreparedStatement psmt=null;
    private ResultSet rs=null;
    public AppointmentJdbc(){}
    public Connection getConn(){
        try{
            if(this.conn==null||this.conn.isClosed()){
                DBConn mc=new DBConn();
                this.conn=mc.getConn();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return conn;
    }
   

public Appointment addAppointment(Appointment appointment){
        String sql="insert into appointments(cid,cname,carnumber,reason,resul) values(?,?,?,?,?)";
        try{
            psmt=this.getConn().prepareStatement(sql);
            
         
          
            psmt.setInt(1,appointment.getCid());
            psmt.setString(2,appointment.getCname());
            psmt.setString(3,appointment.getCarnumber());
            psmt.setString(4,appointment.getReason());
            psmt.setByte(5,appointment.getResul());
       
          
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return appointment;
        } 


public List showAppointment() throws SQLException{
    String sql="select * from appointments";
    List appointmentList=new ArrayList();
    try{
        psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
          Appointment appointment=new Appointment();
            appointment.setAppid(rs.getInt("appid"));
            
            appointment.setCid(rs.getInt("cid"));
            appointment.setCname(rs.getString("cname"));
            appointment.setCarnumber(rs.getString("carnumber"));
            appointment.setReason(rs.getString("reason"));
            appointment.setResul(rs.getByte("resul"));
            appointment.setZuofei(rs.getString("zuofei"));
            
           appointmentList.add(appointment);
   
        }
        return appointmentList;
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
             if(rs!=null){
                   rs.close();
                   rs=null;
                }
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return appointmentList;
} 
public Appointment showOneAppointment(Integer appid){
 ResultSet rs=null;
    String sql="select * from appointments where appid="+appid;
  Appointment appointment=new Appointment();
    try{
      psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
        
               appointment.setAppid(rs.getInt("appid"));
             
               appointment.setCid(rs.getInt("cid"));
               appointment.setCname(rs.getString("cname"));
               appointment.setCarnumber(rs.getString("carnumber"));
               appointment.setReason(rs.getString("reason"));
               appointment.setResul(rs.getByte("resul"));
               appointment.setZuofei(rs.getString("zuofei"));
        }
 
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            psmt.close();
              
            }catch(SQLException e){
                        e.printStackTrace();
                        }
        try{conn.close();
        
        }catch(SQLException e){
             e.printStackTrace();
        }
            }
            return appointment;
 }


public Appointment printAppointment(Integer appid){
 ResultSet rs=null;
    String sql="select * from appointments where appid="+appid;
  Appointment appointment=new Appointment();
    try{
      psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
        
               appointment.setAppid(rs.getInt("appid"));
             
               appointment.setCid(rs.getInt("cid"));
               appointment.setCname(rs.getString("cname"));
               appointment.setCarnumber(rs.getString("carnumber"));
               appointment.setReason(rs.getString("reason"));
               appointment.setResul(rs.getByte("resul"));
               
        }
 
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            psmt.close();
              
            }catch(SQLException e){
                        e.printStackTrace();
                        }
        try{conn.close();
        
        }catch(SQLException e){
             e.printStackTrace();
        }
            }
            return appointment;
 }

public Appointment updateSaveAppointment(Appointment appointment){
    String sql="update appointments set appid=?,cid=?,cname=?,carnumber=?,reason=?,resul=?,zuofei=? where appid="+appointment.getAppid();
 
     try{
            
          psmt=this.getConn().prepareStatement(sql);
          
          psmt.setInt(1,appointment.getAppid());
          psmt.setInt(2,appointment.getCid());
          psmt.setString(3,appointment.getCname());
          psmt.setString(4,appointment.getCarnumber());
          psmt.setString(5,appointment.getReason());
          psmt.setByte(6,appointment.getResul());
           psmt.setString(7,appointment.getZuofei());
      
          
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
         try{
             psmt.close();
            
         }catch(SQLException e){
             e.printStackTrace();
         }
     }
     return appointment;
}
}
